/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.myapp.dbdao;

import com.myapp.struts.bean.EstimateBean;
import com.myapp.struts.form.Estimate5bForm;
import com.myapp.struts.form.EstimateForm;
import com.myapp.util.DBConfig;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author toint - Ha Noi Open University
 * @company LifeTek
 */
public class ReportDBDAO {
    
    /**
     * lay ra danh sach danh gia khac phu hau qua tam thoi
     * @param frm
     * @return
     * @author toint - Ha Noi Open University
     * @since 12/2013
     */
    public static List<EstimateBean> getDataListEstimate5a(EstimateForm frm) throws SQLException, ParseException {
        List<EstimateBean> lstBean = new ArrayList<EstimateBean>();
        String toDate = frm.getToDate();
        String fromDate = frm.getFromDate();
        SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
        Date parseToDate = format.parse(toDate);
        Date parseFromDate = format.parse(fromDate);
        EstimateBean bean;
        if (toDate != null && !"".equals(toDate) && fromDate != null && !"".equals(fromDate)) {
            String sql = "select wa.want_id, wa.code, wa.name, wa.parent_id ";
            sql += ", a.estimate_id, a.unit, a.person_influence, a.person_help";
            sql += ", a.help_total, a.help_spot, a.help_requie, a.size, a.level_poir";
            sql += " from estimate_want wa, estimate_5a a ";
            sql += " where ";
            sql += " wa.want_id = a.want_id ";
            sql += " and wa.status = 1 ";
            sql += " and a.estimate_date >= ? or a.estimate_date <= ?";
            
            Connection conn = null;
            try {
                conn = DBConfig.getConnection();
                PreparedStatement stm = conn.prepareCall(sql);
                stm.setDate(1, new java.sql.Date(parseToDate.getTime()));
                stm.setDate(2, new java.sql.Date(parseFromDate.getTime()));
                ResultSet rs = stm.executeQuery();
                while(rs.next()) {
                    bean = new EstimateBean();
                    bean.setWantId(rs.getInt("want_id"));
                    bean.setWantCode(rs.getString("code"));
                    bean.setWantName(rs.getString("name"));
                    bean.setParentId(rs.getInt("parent_id"));
                    bean.setEstimateId(rs.getInt("estimate_id"));
                    bean.setUnit(rs.getString("unit"));
                    bean.setPersonInfluence(rs.getInt("person_influence"));
                    bean.setPersonHelp(rs.getInt("person_help"));
                    bean.setHelpTotal(rs.getInt("help_total"));
                    bean.setHelpSuport(rs.getInt("help_spot"));
                    bean.setHelpRequie(rs.getInt("help_requie"));
                    bean.setSize(rs.getString("size"));
                    bean.setLevelPoint(rs.getInt("level_poir"));
                    lstBean.add(bean);
                }
            } catch (Exception ex) {
                conn.close();
                System.err.println(ex);
            }
        }
        return lstBean;
    }

    /**
     * ham lay ra danh sach danh gia nhu cau tai thiet theo phuc luc 5b
     * @param frm
     * @return
     * @author toint - Ha Noi Open University
     * @since 12/2013
     */
    public static List<EstimateBean> getDataListEstimate5b(Estimate5bForm frm) throws SQLException, ParseException {
        List<EstimateBean> lstBean = new ArrayList();
        String toDate = frm.getToDate();
        String fromDate = frm.getFromDate();
        SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
        Date pareToDate = format.parse(toDate);
        Date pareFromDate = format.parse(fromDate);
        EstimateBean bean;
        if (toDate != null && !"".equals(toDate) &&
                fromDate != null && !"".equals(fromDate)) {
                String sql = "select wa.want_id, wa.code, wa.name, wa.parent_id ";
                sql += ", b.unit, b.quantity, b.size, b.specification, b.person_benfit ";
                sql += ", b.internal_force, b.state, b.other ";
                sql += " from estimate_want wa, estimate_5b b ";
                sql += " where ";
                sql += " wa.want_id = b.want_id ";
                sql += " and wa.status = 2 ";
                sql += " and b.estimate_date >= ? or b.estimate_date <= ? ";

                Connection conn = null;
                try {
                    conn = DBConfig.getConnection();
                    PreparedStatement st = conn.prepareCall(sql);
                    st.setDate(1, new java.sql.Date(pareToDate.getTime()));
                    st.setDate(2, new java.sql.Date(pareFromDate.getTime()));
                    ResultSet rs = st.executeQuery();
                    while (rs.next()) {
                        bean = new EstimateBean();
                        bean.setEstimateId(rs.getInt("want_id"));
                        bean.setWantCode(rs.getString("code"));
                        bean.setWantName(rs.getString("name"));
                        bean.setParentId(rs.getInt("parent_id"));
                        bean.setUnit(rs.getString("unit"));
                        bean.setQuantity(rs.getFloat("quantity"));
                        bean.setSize(rs.getString("size"));
                        bean.setSpecifitication(rs.getString("specification"));
                        bean.setPersonBenfit(rs.getInt("person_benfit"));
                        bean.setInternalForce(rs.getFloat("internal_force"));
                        bean.setState(rs.getFloat("state"));
                        bean.setOther(rs.getFloat("other"));
                        lstBean.add(bean);
                    }
                } catch (Exception ex) {
                    conn.close();
                }
        }

        return lstBean;
    }

}
